﻿<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
  namespace="Hexa.Core" assembly="Hexa.Core.NHibernate">

  <database-object>
    <create>
      CREATE PROCEDURE DOCUMENT_SCHEMA
      AS
      BEGIN

      --//SQL DATABASE DOCUMENTATION SCRIPT
      --//AUTHOR: NITIN PATEL, EMAIL: NITINPATEL31@GMAIL.COM
      --//DATE:18-FEB-2008
      --//DESCRIPTION: T-SQL SCRIPT TO GENERATE THE DATABASE DOCUMENT FOR SQL SERVER 2000/2005
      DECLARE @I INT, @MAXI INT
      DECLARE @J INT, @MAXJ INT
      DECLARE @K INT, @MAXK INT
      DECLARE @SR INT
      DECLARE @OUTPUT VARCHAR(4000)
      --DECLARE @TMPOUTPUT VARCHAR(MAX)
      DECLARE @SQLVERSION VARCHAR(5)
      DECLARE @LAST VARCHAR(155), @CURRENT VARCHAR(255), @TYP VARCHAR(255), @DESCRIPTION VARCHAR(4000)

      CREATE TABLE #TABLES  (ID INT IDENTITY(1, 1), OBJECT_ID INT, NAME VARCHAR(155), TYPE VARCHAR(20), [DESCRIPTION] VARCHAR(4000))
      CREATE TABLE #COLUMNS (ID INT IDENTITY(1,1), NAME VARCHAR(155), TYPE VARCHAR(155), NULLABLE VARCHAR(2), [DESCRIPTION] VARCHAR(4000))
      CREATE TABLE #FK(ID INT IDENTITY(1,1), NAME VARCHAR(155), COL VARCHAR(155), REFOBJ VARCHAR(155), REFCOL VARCHAR(155))
      CREATE TABLE #CONSTRAINT(ID INT IDENTITY(1,1), NAME VARCHAR(155), COL VARCHAR(155), DEFINITION VARCHAR(1000))
      CREATE TABLE #INDEXES(ID INT IDENTITY(1,1), NAME VARCHAR(155), TYPE VARCHAR(25), COLS VARCHAR(1000))
      CREATE TABLE #STOREDPROCEDURES(ID INT IDENTITY(1, 1), OBJECT_ID INT, NAME VARCHAR(155), TYPE VARCHAR(30), [DESCRIPTION] VARCHAR(4000))
      CREATE TABLE #SPCOLUMNS(ID INT IDENTITY(1,1), NAME VARCHAR(155), TYPE VARCHAR(155), OUTPUT VARCHAR(2))

      IF (SUBSTRING(@@VERSION, 1, 25 ) = 'MICROSOFT SQL SERVER 2005')
      SET @SQLVERSION = '2005'
      ELSE IF (SUBSTRING(@@VERSION, 1, 26 ) = 'MICROSOFT SQL SERVER  2000')
      SET @SQLVERSION = '2000'
      ELSE
      SET @SQLVERSION = '2005'


      PRINT '&lt;HEAD>'
      PRINT '&lt;TITLE>::' + DB_NAME() + '::&lt;/TITLE>'
      PRINT '&lt;STYLE>'

      PRINT '		BODY {'
      PRINT '		FONT-FAMILY:VERDANA;'
      PRINT '		FONT-SIZE:9PT;'
      PRINT '		}'

      PRINT '		TD {'
      PRINT '		FONT-FAMILY:VERDANA;'
      PRINT '		FONT-SIZE:9PT;'
      PRINT '		}'

      PRINT '		TH {'
      PRINT '		FONT-FAMILY:VERDANA;'
      PRINT '		FONT-SIZE:9PT;'
      PRINT '		BACKGROUND:#D3D3D3;'
      PRINT '		}'
      PRINT '		TABLE'
      PRINT '		{'
      PRINT '		BACKGROUND:#D3D3D3;'
      PRINT '		}'
      PRINT '		TR'
      PRINT '		{'
      PRINT '		BACKGROUND:#FFFFFF;'
      PRINT '		}'
      PRINT '	&lt;/STYLE>'
      PRINT '&lt;/HEAD>'
      PRINT '&lt;BODY>'

      SET NOCOUNT ON
      IF @SQLVERSION = '2000'
      BEGIN
      INSERT INTO #TABLES (OBJECT_ID, NAME, TYPE, [DESCRIPTION])
      --FOR 2000
      SELECT OBJECT_ID(TABLE_NAME),  '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']',
      CASE WHEN TABLE_TYPE = 'BASE TABLE'  THEN 'TABLE'   ELSE 'VIEW' END,
      CAST(P.VALUE AS VARCHAR(4000))
      FROM INFORMATION_SCHEMA.TABLES T
      LEFT OUTER JOIN SYSPROPERTIES P ON P.ID = OBJECT_ID(T.TABLE_NAME) AND SMALLID = 0 AND P.NAME = 'MS_DESCRIPTION'
      ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME
      END
      ELSE IF @SQLVERSION = '2005'
      BEGIN
      INSERT INTO #TABLES (OBJECT_ID, NAME, TYPE, [DESCRIPTION])
      --FOR 2005
      SELECT O.OBJECT_ID,  '[' + S.NAME + '].[' + O.NAME + ']',
      CASE WHEN TYPE = 'V' THEN 'VIEW' WHEN TYPE = 'U' THEN 'TABLE' END,
      CAST(P.VALUE AS VARCHAR(4000))
      FROM SYS.OBJECTS O
      LEFT OUTER JOIN SYS.SCHEMAS S ON S.SCHEMA_ID = O.SCHEMA_ID
      LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES P ON P.MAJOR_ID = O.OBJECT_ID AND MINOR_ID = 0 AND P.NAME = 'MS_DESCRIPTION'
      WHERE TYPE IN ('U', 'V')
      ORDER BY TYPE, S.NAME, O.NAME
      END
      SET @MAXI = @@ROWCOUNT
      SET @I = 1

      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="550PX" >&lt;TR>&lt;TD COLSPAN="3" STYLE="HEIGHT:50;FONT-SIZE:14PT;TEXT-ALIGN:CENTER;">&lt;A NAME="INDEX">&lt;/A>&lt;B>INDEX&lt;/B>&lt;/TD>&lt;/TR>&lt;/TABLE>'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="1" CELLPADDING="0" WIDTH="550PX">&lt;TR>&lt;TH>SR&lt;/TH>&lt;TH>OBJECT&lt;/TH>&lt;TH>TYPE&lt;/TH>&lt;/TR>'
      WHILE(@I &lt;= @MAXI)
      BEGIN
      SELECT @OUTPUT =  '&lt;TR>&lt;TD ALIGN="CENTER">' + CAST((@I) AS VARCHAR) + '&lt;/TD>&lt;TD>&lt;A HREF="#' + TYPE + ':' + NAME + '">' + NAME + '&lt;/A>&lt;/TD>&lt;TD>' + TYPE + '&lt;/TD>&lt;/TR>'
      FROM #TABLES WHERE ID = @I

      PRINT @OUTPUT
      SET @I = @I + 1
      END

      --LOOK IN THE SYS OBJECTS TABLE FOR OBJECTS OF TYPE 'P' STORED PROCEDURES AND NOT CATEGORY 2
      --(CATEGORY 2 SEEM TO BE SYSTEM STORED PROCEDURES) ALSO LOOK IN THE SYSPROPERTIES
      --TABLE FOR ANY DESCRIPTION  OF THE STORED PROCEDURE AND INSERT INTO THE TEMP TABLE
      IF @SQLVERSION = '2005'
      BEGIN
      INSERT INTO #STOREDPROCEDURES(OBJECT_ID, NAME, TYPE, [DESCRIPTION])
      SELECT SO.ID, '[DBO].[' + CAST(SO.NAME AS VARCHAR(155)) + ']', 'STORED PROCEDURE', (SELECT CAST(SP.NAME AS VARCHAR(4000)) FROM SYSOBJECTS SP WHERE SP.ID = SO.ID) AS COMMENT
      FROM SYSOBJECTS SO WHERE SO.TYPE ='P' AND SO.CATEGORY &lt;> 2
      END

      SET @MAXK = (SELECT COUNT(ID) FROM #STOREDPROCEDURES)
      SET @K = 1
      WHILE(@K &lt;= @MAXK)
      BEGIN
      SELECT @OUTPUT =  '&lt;TR>&lt;TD ALIGN="CENTER">' + CAST((@K) AS VARCHAR) + '&lt;/TD>&lt;TD>&lt;A HREF="#' + TYPE + ':' + NAME + '">' + NAME + '&lt;/A>&lt;/TD>&lt;TD>' + TYPE + '&lt;/TD>&lt;/TR>'
      FROM #STOREDPROCEDURES WHERE ID = @K
      PRINT @OUTPUT
      SET @K = @K + 1
      END
      PRINT '&lt;/TABLE>&lt;BR />'

      SET @I = 1
      WHILE(@I &lt;= @MAXI)
      BEGIN
      --TABLE HEADER
      SELECT @OUTPUT =  '&lt;TR>&lt;TH ALIGN="LEFT">&lt;A NAME="' + TYPE + ':' + NAME + '">&lt;/A>&lt;B>' + TYPE + ':' + NAME + '&lt;/B>&lt;/TH>&lt;/TR>',  @DESCRIPTION = [DESCRIPTION]
      FROM #TABLES WHERE ID = @I

      PRINT '&lt;BR />&lt;BR />&lt;BR />&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD ALIGN="RIGHT">&lt;A HREF="#INDEX">INDEX&lt;/A>&lt;/TD>&lt;/TR>'
      PRINT @OUTPUT
      PRINT '&lt;/TABLE>&lt;BR />'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD>&lt;B>DESCRIPTION&lt;/B>&lt;/TD>&lt;/TR>&lt;TR>&lt;TD>' + ISNULL(@DESCRIPTION, '') + '&lt;/TD>&lt;/TR>&lt;/TABLE>&lt;BR />'

      --TABLE COLUMNS
      TRUNCATE TABLE #COLUMNS
      IF @SQLVERSION = '2000'
      BEGIN
      INSERT INTO #COLUMNS  (NAME, TYPE, NULLABLE, [DESCRIPTION])
      --FOR 2000
      SELECT C.NAME,
      TYPE_NAME(XTYPE) + (
      CASE WHEN (TYPE_NAME(XTYPE) = 'VARCHAR' OR TYPE_NAME(XTYPE) = 'NVARCHAR' OR TYPE_NAME(XTYPE) ='CHAR' OR TYPE_NAME(XTYPE) ='NCHAR')
      THEN '(' + CAST(LENGTH AS VARCHAR) + ')'
      WHEN TYPE_NAME(XTYPE) = 'DECIMAL'
      THEN '(' + CAST(PREC AS VARCHAR) + ',' + CAST(SCALE AS VARCHAR)   + ')'
      ELSE ''
      END
      ),
      CASE WHEN ISNULLABLE = 1 THEN 'Y' ELSE 'N'  END,
      CAST(P.VALUE AS VARCHAR(8000))
      FROM SYSCOLUMNS C
      INNER JOIN #TABLES T ON T.OBJECT_ID = C.ID
      LEFT OUTER JOIN SYSPROPERTIES P ON P.ID = C.ID AND P.SMALLID = C.COLID AND P.NAME = 'MS_DESCRIPTION'
      WHERE T.ID = @I
      ORDER BY C.COLORDER
      END
      ELSE IF @SQLVERSION = '2005'
      BEGIN
      INSERT INTO #COLUMNS  (NAME, TYPE, NULLABLE, [DESCRIPTION])
      --FOR 2005
      SELECT C.NAME,
      TYPE_NAME(USER_TYPE_ID) + (
      CASE WHEN (TYPE_NAME(USER_TYPE_ID) = 'VARCHAR' OR TYPE_NAME(USER_TYPE_ID) = 'NVARCHAR' OR TYPE_NAME(USER_TYPE_ID) ='CHAR' OR TYPE_NAME(USER_TYPE_ID) ='NCHAR')
      THEN '(' + CAST(MAX_LENGTH AS VARCHAR) + ')'
      WHEN TYPE_NAME(USER_TYPE_ID) = 'DECIMAL'
      THEN '(' + CAST([PRECISION] AS VARCHAR) + ',' + CAST(SCALE AS VARCHAR)   + ')'
      ELSE ''
      END
      ),
      CASE WHEN IS_NULLABLE = 1 THEN 'Y' ELSE 'N'  END,
      CAST(P.VALUE AS VARCHAR(4000))
      FROM SYS.COLUMNS C
      INNER JOIN #TABLES T ON T.OBJECT_ID = C.OBJECT_ID
      LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES P ON P.MAJOR_ID = C.OBJECT_ID AND P.MINOR_ID  = C.COLUMN_ID AND P.NAME = 'MS_DESCRIPTION'
      WHERE T.ID = @I
      ORDER BY C.COLUMN_ID
      END
      SET @MAXJ =   @@ROWCOUNT
      SET @J = 1

      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD>&lt;B>TABLE COLUMNS&lt;/B>&lt;/TD>&lt;/TR>&lt;/TABLE>'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="1" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TH>SR.&lt;/TH>&lt;TH>NAME&lt;/TH>&lt;TH>DATATYPE&lt;/TH>&lt;TH>NULLABLE&lt;/TH>&lt;TH>DESCRIPTION&lt;/TH>&lt;/TR>'

      WHILE(@J &lt;= @MAXJ)
      BEGIN
      SELECT @OUTPUT = '&lt;TR>&lt;TD WIDTH="20PX" ALIGN="CENTER">' + CAST((@J) AS VARCHAR) + '&lt;/TD>&lt;TD WIDTH="150PX">' + ISNULL(NAME,'')  + '&lt;/TD>&lt;TD WIDTH="150PX">' +  UPPER(ISNULL(TYPE,'')) + '&lt;/TD>&lt;TD WIDTH="50PX" ALIGN="CENTER">' + ISNULL(NULLABLE,'N') + '&lt;/TD>&lt;TD>' + ISNULL([DESCRIPTION],'') + '&lt;/TD>&lt;/TR>'
      FROM #COLUMNS  WHERE ID = @J

      PRINT 	@OUTPUT
      SET @J = @J + 1;
      END



      PRINT '&lt;/TABLE>&lt;BR />'

      --REFERENCE KEY
      TRUNCATE TABLE #FK
      IF @SQLVERSION = '2000'
      BEGIN
      INSERT INTO #FK  (NAME, COL, REFOBJ, REFCOL)
      --		FOR 2000
      SELECT OBJECT_NAME(CONSTID), S.NAME,  OBJECT_NAME(RKEYID) ,  S1.NAME
      FROM SYSFOREIGNKEYS F
      INNER JOIN SYSOBJECTS O ON O.ID = F.CONSTID
      INNER JOIN SYSCOLUMNS S ON S.ID = F.FKEYID AND S.COLORDER = F.FKEY
      INNER JOIN SYSCOLUMNS S1 ON S1.ID = F.RKEYID AND S1.COLORDER = F.RKEY
      INNER JOIN #TABLES T ON T.OBJECT_ID = F.FKEYID
      WHERE T.ID = @I
      ORDER BY 1
      END
      ELSE IF @SQLVERSION = '2005'
      BEGIN
      INSERT INTO #FK  (NAME, COL, REFOBJ, REFCOL)
      --		FOR 2005
      SELECT F.NAME, COL_NAME (FC.PARENT_OBJECT_ID, FC.PARENT_COLUMN_ID) , OBJECT_NAME(FC.REFERENCED_OBJECT_ID) , COL_NAME (FC.REFERENCED_OBJECT_ID, FC.REFERENCED_COLUMN_ID)
      FROM SYS.FOREIGN_KEYS F
      INNER  JOIN  SYS.FOREIGN_KEY_COLUMNS  FC  ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
      INNER JOIN #TABLES T ON T.OBJECT_ID = F.PARENT_OBJECT_ID
      WHERE T.ID = @I
      ORDER BY F.NAME
      END

      SET @MAXJ =   @@ROWCOUNT
      SET @J = 1
      IF (@MAXJ >0)
      BEGIN

      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD>&lt;B>REFRENCE KEYS&lt;/B>&lt;/TD>&lt;/TR>&lt;/TABLE>'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="1" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TH>SR.&lt;/TH>&lt;TH>NAME&lt;/TH>&lt;TH>COLUMN&lt;/TH>&lt;TH>REFERENCE TO&lt;/TH>&lt;/TR>'

      WHILE(@J &lt;= @MAXJ)
      BEGIN

      SELECT @OUTPUT = '&lt;TR>&lt;TD WIDTH="20PX" ALIGN="CENTER">' + CAST((@J) AS VARCHAR) + '&lt;/TD>&lt;TD WIDTH="150PX">' + ISNULL(NAME,'')  + '&lt;/TD>&lt;TD WIDTH="150PX">' +  ISNULL(COL,'') + '&lt;/TD>&lt;TD>[' + ISNULL(REFOBJ,'N') + '].[' +  ISNULL(REFCOL,'N') + ']&lt;/TD>&lt;/TR>'
      FROM #FK  WHERE ID = @J

      PRINT @OUTPUT
      SET @J = @J + 1;
      END

      PRINT '&lt;/TABLE>&lt;BR />'
      END

      --DEFAULT CONSTRAINTS
      TRUNCATE TABLE #CONSTRAINT
      IF @SQLVERSION = '2000'
      BEGIN
      INSERT INTO #CONSTRAINT  (NAME, COL, DEFINITION)
      SELECT OBJECT_NAME(C.CONSTID), COL_NAME(C.ID, C.COLID), S.TEXT
      FROM SYSCONSTRAINTS C
      INNER JOIN #TABLES T ON T.OBJECT_ID = C.ID
      LEFT OUTER JOIN SYSCOMMENTS S ON S.ID = C.CONSTID
      WHERE T.ID = @I
      AND
      CONVERT(VARCHAR,+ (C.STATUS &amp; 1)/1)
      + CONVERT(VARCHAR,(C.STATUS &amp; 2)/2)
      + CONVERT(VARCHAR,(C.STATUS &amp; 4)/4)
      + CONVERT(VARCHAR,(C.STATUS &amp; 8)/8)
      + CONVERT(VARCHAR,(C.STATUS &amp; 16)/16)
      + CONVERT(VARCHAR,(C.STATUS &amp; 32)/32)
      + CONVERT(VARCHAR,(C.STATUS &amp; 64)/64)
      + CONVERT(VARCHAR,(C.STATUS &amp; 128)/128) = '10101000'
      END
      ELSE IF @SQLVERSION = '2005'
      BEGIN
      INSERT INTO #CONSTRAINT  (NAME, COL, DEFINITION)
      SELECT C.NAME,  COL_NAME(PARENT_OBJECT_ID, PARENT_COLUMN_ID), C.DEFINITION
      FROM SYS.DEFAULT_CONSTRAINTS C
      INNER JOIN #TABLES T ON T.OBJECT_ID = C.PARENT_OBJECT_ID
      WHERE T.ID = @I
      ORDER BY C.NAME
      END
      SET @MAXJ =   @@ROWCOUNT
      SET @J = 1
      IF (@MAXJ >0)
      BEGIN

      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD>&lt;B>DEFAULT CONSTRAINTS&lt;/B>&lt;/TD>&lt;/TR>&lt;/TABLE>'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="1" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TH>SR.&lt;/TH>&lt;TH>NAME&lt;/TH>&lt;TH>COLUMN&lt;/TH>&lt;TH>VALUE&lt;/TH>&lt;/TR>'

      WHILE(@J &lt;= @MAXJ)
      BEGIN

      SELECT @OUTPUT = '&lt;TR>&lt;TD WIDTH="20PX" ALIGN="CENTER">' + CAST((@J) AS VARCHAR) + '&lt;/TD>&lt;TD WIDTH="250PX">' + ISNULL(NAME,'')  + '&lt;/TD>&lt;TD WIDTH="150PX">' +  ISNULL(COL,'') + '&lt;/TD>&lt;TD>' +  ISNULL(DEFINITION,'') + '&lt;/TD>&lt;/TR>'
      FROM #CONSTRAINT  WHERE ID = @J

      PRINT @OUTPUT
      SET @J = @J + 1;
      END

      PRINT '&lt;/TABLE>&lt;BR />'
      END


      --CHECK  CONSTRAINTS
      TRUNCATE TABLE #CONSTRAINT
      IF @SQLVERSION = '2000'
      BEGIN
      INSERT INTO #CONSTRAINT  (NAME, COL, DEFINITION)
      SELECT OBJECT_NAME(C.CONSTID), COL_NAME(C.ID, C.COLID), S.TEXT
      FROM SYSCONSTRAINTS C
      INNER JOIN #TABLES T ON T.OBJECT_ID = C.ID
      LEFT OUTER JOIN SYSCOMMENTS S ON S.ID = C.CONSTID
      WHERE T.ID = @I
      AND ( CONVERT(VARCHAR,+ (C.STATUS &amp; 1)/1)
      + CONVERT(VARCHAR,(C.STATUS &amp; 2)/2)
      + CONVERT(VARCHAR,(C.STATUS &amp; 4)/4)
      + CONVERT(VARCHAR,(C.STATUS &amp; 8)/8)
      + CONVERT(VARCHAR,(C.STATUS &amp; 16)/16)
      + CONVERT(VARCHAR,(C.STATUS &amp; 32)/32)
      + CONVERT(VARCHAR,(C.STATUS &amp; 64)/64)
      + CONVERT(VARCHAR,(C.STATUS &amp; 128)/128) = '00101000'
      OR CONVERT(VARCHAR,+ (C.STATUS &amp; 1)/1)
      + CONVERT(VARCHAR,(C.STATUS &amp; 2)/2)
      + CONVERT(VARCHAR,(C.STATUS &amp; 4)/4)
      + CONVERT(VARCHAR,(C.STATUS &amp; 8)/8)
      + CONVERT(VARCHAR,(C.STATUS &amp; 16)/16)
      + CONVERT(VARCHAR,(C.STATUS &amp; 32)/32)
      + CONVERT(VARCHAR,(C.STATUS &amp; 64)/64)
      + CONVERT(VARCHAR,(C.STATUS &amp; 128)/128) = '00100100')

      END
      ELSE IF @SQLVERSION = '2005'
      BEGIN
      INSERT INTO #CONSTRAINT  (NAME, COL, DEFINITION)
      SELECT C.NAME,  COL_NAME(PARENT_OBJECT_ID, PARENT_COLUMN_ID), DEFINITION
      FROM SYS.CHECK_CONSTRAINTS C
      INNER JOIN #TABLES T ON T.OBJECT_ID = C.PARENT_OBJECT_ID
      WHERE T.ID = @I
      ORDER BY C.NAME
      END
      SET @MAXJ =   @@ROWCOUNT

      SET @J = 1
      IF (@MAXJ >0)
      BEGIN

      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD>&lt;B>CHECK  CONSTRAINTS&lt;/B>&lt;/TD>&lt;/TR>&lt;/TABLE>'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="1" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TH>SR.&lt;/TH>&lt;TH>NAME&lt;/TH>&lt;TH>COLUMN&lt;/TH>&lt;TH>DEFINITION&lt;/TH>&lt;/TR>'

      WHILE(@J &lt;= @MAXJ)
      BEGIN

      SELECT @OUTPUT = '&lt;TR>&lt;TD WIDTH="20PX" ALIGN="CENTER">' + CAST((@J) AS VARCHAR) + '&lt;/TD>&lt;TD WIDTH="250PX">' + ISNULL(NAME,'')  + '&lt;/TD>&lt;TD WIDTH="150PX">' +  ISNULL(COL,'') + '&lt;/TD>&lt;TD>' +  ISNULL(DEFINITION,'') + '&lt;/TD>&lt;/TR>'
      FROM #CONSTRAINT  WHERE ID = @J
      PRINT @OUTPUT
      SET @J = @J + 1;
      END

      PRINT '&lt;/TABLE>&lt;BR />'
      END


      --TRIGGERS
      TRUNCATE TABLE #CONSTRAINT
      IF @SQLVERSION = '2000'
      BEGIN
      INSERT INTO #CONSTRAINT  (NAME)
      SELECT TR.NAME
      FROM SYSOBJECTS TR
      INNER JOIN #TABLES T ON T.OBJECT_ID = TR.PARENT_OBJ
      WHERE T.ID = @I AND TR.TYPE = 'TR'
      ORDER BY TR.NAME
      END
      ELSE IF @SQLVERSION = '2005'
      BEGIN
      INSERT INTO #CONSTRAINT  (NAME)
      SELECT TR.NAME
      FROM SYS.TRIGGERS TR
      INNER JOIN #TABLES T ON T.OBJECT_ID = TR.PARENT_ID
      WHERE T.ID = @I
      ORDER BY TR.NAME
      END
      SET @MAXJ =   @@ROWCOUNT



      SET @J = 1
      IF (@MAXJ >0)
      BEGIN

      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD>&lt;B>TRIGGERS&lt;/B>&lt;/TD>&lt;/TR>&lt;/TABLE>'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="1" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TH>SR.&lt;/TH>&lt;TH>NAME&lt;/TH>&lt;TH>DESCRIPTION&lt;/TH>&lt;/TR>'

      WHILE(@J &lt;= @MAXJ)
      BEGIN
      SELECT @OUTPUT = '&lt;TR>&lt;TD WIDTH="20PX" ALIGN="CENTER">' + CAST((@J) AS VARCHAR) + '&lt;/TD>&lt;TD WIDTH="150PX">' + ISNULL(NAME,'')  + '&lt;/TD>&lt;TD>&lt;/TD>&lt;/TR>'
      FROM #CONSTRAINT  WHERE ID = @J
      PRINT @OUTPUT
      SET @J = @J + 1;
      END

      PRINT '&lt;/TABLE>&lt;BR />'
      END

      --INDEXES
      TRUNCATE TABLE #INDEXES
      IF @SQLVERSION = '2000'
      BEGIN
      INSERT INTO #INDEXES  (NAME, TYPE, COLS)
      SELECT I.NAME, CASE WHEN I.INDID = 0 THEN 'HEAP' WHEN I.INDID = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END , C.NAME
      FROM SYSINDEXES I
      INNER JOIN SYSINDEXKEYS K  ON K.INDID = I.INDID  AND K.ID = I.ID
      INNER JOIN SYSCOLUMNS C ON C.ID = K.ID AND C.COLORDER = K.COLID
      INNER JOIN #TABLES T ON T.OBJECT_ID = I.ID
      WHERE T.ID = @I AND I.NAME NOT LIKE '_WA%'
      ORDER BY I.NAME, I.KEYCNT
      END
      ELSE IF @SQLVERSION = '2005'
      BEGIN
      INSERT INTO #INDEXES  (NAME, TYPE, COLS)
      SELECT I.NAME, CASE WHEN I.TYPE = 0 THEN 'HEAP' WHEN I.TYPE = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END,  COL_NAME(I.OBJECT_ID, C.COLUMN_ID)
      FROM SYS.INDEXES I
      INNER JOIN SYS.INDEX_COLUMNS C ON I.INDEX_ID = C.INDEX_ID AND C.OBJECT_ID = I.OBJECT_ID
      INNER JOIN #TABLES T ON T.OBJECT_ID = I.OBJECT_ID
      WHERE T.ID = @I
      ORDER BY I.NAME, C.COLUMN_ID
      END

      SET @MAXJ =   @@ROWCOUNT

      SET @J = 1
      SET @SR = 1
      IF (@MAXJ >0)
      BEGIN

      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD>&lt;B>INDEXES&lt;/B>&lt;/TD>&lt;/TR>&lt;/TABLE>'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="1" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TH>SR.&lt;/TH>&lt;TH>NAME&lt;/TH>&lt;TH>TYPE&lt;/TH>&lt;TH>COLUMNS&lt;/TH>&lt;/TR>'
      SET @OUTPUT = ''
      SET @LAST = ''
      SET @CURRENT = ''
      WHILE(@J &lt;= @MAXJ)
      BEGIN
      SELECT @CURRENT = ISNULL(NAME,'') FROM #INDEXES  WHERE ID = @J

      IF @LAST &lt;> @CURRENT  AND @LAST &lt;> ''
      BEGIN
      PRINT '&lt;TR>&lt;TD WIDTH="20PX" ALIGN="CENTER">' + CAST((@SR) AS VARCHAR) + '&lt;/TD>&lt;TD WIDTH="150PX">' + @LAST + '&lt;/TD>&lt;TD WIDTH="150PX">' + @TYP + '&lt;/TD>&lt;TD>' + @OUTPUT  + '&lt;/TD>&lt;/TR>'
      SET @OUTPUT  = ''
      SET @SR = @SR + 1
      END


      SELECT @OUTPUT = @OUTPUT + COLS + '&lt;BR />' , @TYP = TYPE
      FROM #INDEXES  WHERE ID = @J

      SET @LAST = @CURRENT
      SET @J = @J + 1;
      END
      IF @OUTPUT &lt;> ''
      BEGIN
      PRINT '&lt;TR>&lt;TD WIDTH="20PX" ALIGN="CENTER">' + CAST((@SR) AS VARCHAR) + '&lt;/TD>&lt;TD WIDTH="150PX">' + @LAST + '&lt;/TD>&lt;TD WIDTH="150PX">' + @TYP + '&lt;/TD>&lt;TD>' + @OUTPUT  + '&lt;/TD>&lt;/TR>'
      END

      PRINT '&lt;/TABLE>&lt;BR />'
      END

      SET @I = @I + 1;
      --PRINT @OUTPUT
      END

      SET @K = 1
      WHILE(@K &lt;= @MAXK)
      BEGIN
      --TABLE HEADER
      SELECT @OUTPUT =  '&lt;TR>&lt;TH ALIGN="LEFT">&lt;A NAME="' + TYPE + ':' + NAME + '">&lt;/A>&lt;B>' + TYPE + ':' + NAME + '&lt;/B>&lt;/TH>&lt;/TR>',  @DESCRIPTION = [DESCRIPTION]
      FROM #STOREDPROCEDURES WHERE ID = @K

      PRINT '&lt;BR />&lt;BR />&lt;BR />&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD ALIGN="RIGHT">&lt;A HREF="#INDEX">INDEX&lt;/A>&lt;/TD>&lt;/TR>'
      PRINT @OUTPUT
      PRINT '&lt;/TABLE>&lt;BR />'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD>&lt;B>DESCRIPTION&lt;/B>&lt;/TD>&lt;/TR>&lt;TR>&lt;TD>' + ISNULL(@DESCRIPTION, '') + '&lt;/TD>&lt;/TR>&lt;/TABLE>&lt;BR />'

      --TABLE SPCOLUMNS
      TRUNCATE TABLE #SPCOLUMNS
      IF @SQLVERSION = '2005'
      BEGIN
      -- LOOK IN THE SYSCOLOUMNS TABLE TO GET THE COLOUMN (INPUT, OUTPUT PARAMETERS) NAMES
      INSERT INTO #SPCOLUMNS  (NAME, TYPE, OUTPUT)
      --FOR 2000
      SELECT SC.NAME, ST.NAME + (
      CASE WHEN (ST.NAME = 'VARCHAR' OR ST.NAME = 'NVARCHAR' OR ST.NAME ='CHAR' OR ST.NAME ='NCHAR')
      THEN '(' + CAST(SC.LENGTH AS VARCHAR) + ')'
      WHEN ST.NAME = 'DECIMAL'
      THEN '(' + CAST(SC.PREC AS VARCHAR) + ',' + CAST(SC.SCALE AS VARCHAR)   + ')'
      ELSE ''
      END	)
      , ISOUTPARAM
      FROM SYSCOLUMNS SC, MASTER..SYSTYPES ST, SYSOBJECTS SO
      WHERE SO.ID = (SELECT SP.OBJECT_ID FROM #STOREDPROCEDURES SP
      WHERE SP.ID = @K)
      AND  SC.ID = SO.ID
      AND SC.XTYPE = ST.XTYPE
      END
      SET @MAXJ =   @@ROWCOUNT
      SET @J = 1

      PRINT '&lt;TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TD>&lt;B>TABLE COLUMNS&lt;/B>&lt;/TD>&lt;/TR>&lt;/TABLE>'
      PRINT '&lt;TABLE BORDER="0" CELLSPACING="1" CELLPADDING="0" WIDTH="750PX">&lt;TR>&lt;TH>SR.&lt;/TH>&lt;TH>NAME&lt;/TH>&lt;TH>DATATYPE&lt;/TH>&lt;TH>OUTPUT&lt;/TH>&lt;/TR>'

      WHILE(@J &lt;= @MAXJ)
      BEGIN
      SELECT @OUTPUT = '&lt;TR>&lt;TD WIDTH="20PX" ALIGN="CENTER">' + CAST((@J) AS VARCHAR) + '&lt;/TD>&lt;TD WIDTH="150PX">' + ISNULL(NAME,'')  + '&lt;/TD>&lt;TD WIDTH="150PX">' +  UPPER(ISNULL(TYPE,'')) + '&lt;/TD>&lt;TD WIDTH="50PX" ALIGN="CENTER">' + ISNULL(OUTPUT,'0') + '&lt;/TD>&lt;/TR>'
      FROM #SPCOLUMNS  WHERE ID = @J

      PRINT 	@OUTPUT
      SET @J = @J + 1;
      END

      PRINT '&lt;/TABLE>&lt;BR />'
      SET @K = @K + 1;
      END


      PRINT '&lt;/BODY>'
      PRINT '&lt;/HTML>'

      DROP TABLE #TABLES
      DROP TABLE #COLUMNS
      DROP TABLE #FK
      DROP TABLE #CONSTRAINT
      DROP TABLE #INDEXES
      DROP TABLE #STOREDPROCEDURES
      DROP TABLE #SPCOLUMNS
      SET NOCOUNT OFF

      END
      GO
    </create>
    <drop>
      IF EXISTS (
      SELECT name FROM sysobjects
      WHERE name = 'DOCUMENT_SCHEMA' AND type = 'P')

      DROP PROCEDURE DOCUMENT_SCHEMA
    </drop>
    <dialect-scope name='NHibernate.Dialect.MsSql2008Dialect' />
  </database-object>

</hibernate-mapping>